package com.kaifamiao.jdbc.data.dao;

import com.kaifamiao.jdbc.data.entity.Lion;
import com.kaifamiao.jdbc.helper.JdbcHelper;
import com.kaifamiao.jdbc.helper.RowHolder;

import java.time.LocalDate;
import java.util.List;

/**
 * DAO: Data Access Object, 数据访问对象
 * 这里的 LionDAO 类的实例 专门负责 在 Lion类的实例 和 t_lions表的数据行 之间起 勾搭作用
 */
public class LionDAO {

    private static final String SELECT_BASE = "SELECT id, name, birthdate, hometown FROM t_lions";
    private static final String SELECT_ONE = SELECT_BASE + " WHERE id = ? ";
    private static final String SELECT_ALL = SELECT_BASE + " ORDER BY id";
    // private static final String SELECT_MAX_ID = "SELECT ifnull( max(id), 0 ) FROM t_lions"; // MySQL
    //private static final String SELECT_MAX_ID = "SELECT nvl( max(id), 0 ) FROM t_lions"; // Oracle
    private static final String SELECT_MAX_ID = "SELECT max(id) maxid FROM t_lions";

    private static final String INSERT = "INSERT INTO t_lions ( name, birthdate, hometown, id ) VALUES ( ?, ?, ?, ? )";
    private static final String UPDATE = "UPDATE t_lions SET name = ? , birthdate = ? , hometown = ? WHERE id = ?";
    private static final String DELETE = "DELETE FROM t_lions WHERE id = ?";

    private static final RowHolder<Lion> ROW_HOLDER = rs -> {
        Lion lion = new Lion();
        lion.setId( rs.getInt("id") );
        lion.setName( rs.getString("name") );
        java.sql.Date date = rs.getDate( "birthdate" );
        LocalDate hiredate = date == null ? null : date.toLocalDate();
        lion.setBirthdate( hiredate );
        lion.setHometown( rs.getString("hometown") );
        return lion;
    };

    private static final RowHolder<Integer> ID_HOLDER = rs -> rs.getInt( 1 );

    public Lion find( Integer id ) {
        // 获得JdbcHelper实例
        JdbcHelper h = JdbcHelper.getInstance();
        // 基于JdbcHelper实例完成查询操作
        Lion x = h.single( SELECT_ONE , ROW_HOLDER , id );
        // 释放资源
        h.release();
        return  x ;
    }

    public List<Lion> findAll(){
        // 获得JdbcHelper实例
        JdbcHelper h = JdbcHelper.getInstance();
        // 基于JdbcHelper实例完成查询操作
        List<Lion> list = h.query( SELECT_ALL , ROW_HOLDER );
        // 释放资源
        h.release();
        return list;
    }

    public boolean save( Lion x ) {
        JdbcHelper h = JdbcHelper.getInstance();

        // 执行查询语句获得数据库中已经存在的主键最大值
        Integer maxId = h.single( SELECT_MAX_ID, ID_HOLDER );
        // 若 maxId 为 空则将 零 赋值给 maxId
        maxId = maxId == null ? 0 : maxId;

        // 将新的ID设置到 x 指向的实例中
        x.setId( maxId + 1 );

        LocalDate birthdate = x.getBirthdate();
        java.sql.Date date = birthdate == null ? null : java.sql.Date.valueOf( birthdate );
        // 依次将参数占位符的值添加到数组中
        Object[] params = { x.getName(), date , x.getHometown(), x.getId() };

        // 基于JdbcHelper实例完成查询操作
        int count = h.execute( INSERT, params );
        h.release();
        return count == 1 ;
    }

    public boolean update( Lion x ) {
        JdbcHelper h = JdbcHelper.getInstance();
        LocalDate birthdate = x.getBirthdate();
        java.sql.Date date = birthdate == null ? null : java.sql.Date.valueOf( birthdate );
        Object[] params = { x.getName(), date, x.getHometown(), x.getId() };
        int count = h.execute( UPDATE, params );
        h.release();
        return count == 1 ;
    }

    public boolean delete( Integer id ) {
        JdbcHelper h = JdbcHelper.getInstance();
        int count = h.execute( DELETE, id );
        h.release();
        return count == 1 ;
    }

}
